package com.bsoft.gol.hcs.clinical.dao;


import ctd.persistence.annotation.DAOMethod;
import ctd.persistence.annotation.DAOParam;
import ctd.persistence.support.hibernate.HibernateSupportDelegateDAO;
import ctd.persistence.support.hibernate.template.AbstractHibernateStatelessResultAction;
import ctd.persistence.support.hibernate.template.HibernateSessionTemplate;
import ctd.persistence.support.hibernate.template.HibernateStatelessResultAction;
import ctd.util.converter.ConversionUtils;
import hcn.base.drug.BaseOrgDrug;
import hcn.base.drug.qo.BaseDrugManagementQo;
import hcn.base.drug.qo.OrgRegionQo;
import hcn.base.drug.vo.BaseDrugBeanVo;
import hcn.base.drug.vo.OrgRegionVo;
import hcn.util.validation.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.StatelessSession;
import org.hibernate.criterion.CriteriaSpecification;
import com.bsoft.gol.hcs.utils.CommonUtils;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public abstract class BaseOrgDrugDAO extends HibernateSupportDelegateDAO<BaseOrgDrug> {

    public BaseOrgDrugDAO() {
        this.setKeyField("objectDrugId");
    }

    @DAOMethod(sql = " from BaseOrgDrug where orgDrugId=:orgDrugId and objectId=:objectId")
    public abstract BaseOrgDrug getBaseOrgDrugByCodeAndObj(@DAOParam("orgDrugId") String orgDrugId, @DAOParam("objectId") String objectId);

    @DAOMethod(sql = " update BaseOrgDrug set status=:status,enableUser=:enableUser,enableDt=now() where objectDrugId=:objectDrugId")
    public abstract void updateEnableStatusById(@DAOParam("objectDrugId") Integer objectDrugId, @DAOParam("status") String status, @DAOParam("enableUser") String enableUser);

    @DAOMethod(sql = " update BaseOrgDrug set status=:status,modifyUser=:modifyUser,modifyDt=now() where objectDrugId=:objectDrugId")
    public abstract void updateStatusById(@DAOParam("objectDrugId") Integer objectDrugId, @DAOParam("status") String status, @DAOParam("modifyUser") String modifyUser);

    @DAOMethod(sql = " from BaseOrgDrug where objectDrugId=:objectDrugId")
    public abstract BaseOrgDrug getBaseOrgDrugById(@DAOParam("objectDrugId") Integer objectDrugId);

    @DAOMethod(sql = " from BaseOrgDrug where objectDrugId in(:objectDrugIds)")
    public abstract List<BaseOrgDrug> findDrugs(@DAOParam("objectDrugIds") List<Integer> objectDrugIds);

    @DAOMethod(sql = " from BaseOrgDrug where objectDrugId in(:objectDrugIds) and objectType='1' and deleteStatus='0' ")
    public abstract List<BaseOrgDrug> findDrugsByOrg(@DAOParam("objectDrugIds")List<Integer> objectDrugIds);

    @DAOMethod(sql = " from BaseOrgDrug where objectId=:objectId and orgDrugName=:orgDrugName and orgDrugId=:orgDrugId and objectType='1' and deleteStatus='0'")
    public abstract List<BaseOrgDrug> findDrugByOrgId(@DAOParam("objectId") String objectId,@DAOParam("orgDrugName")String orgDrugName,@DAOParam("orgDrugId")String orgDrugId);


    public List<OrgRegionVo> queryOrgRegionDrugList(final OrgRegionQo qo) {
        HibernateStatelessResultAction<List<OrgRegionVo>> action = new AbstractHibernateStatelessResultAction<List<OrgRegionVo>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select d.org_drug_name as orgDrugName,d.specification,d.org_factory_id as orgFactoryId,d.org_factory_name as orgFactoryName,d.org_drug_id as orgDrugId,");
                sql.append("d.dosage_form_code as dosageFormCode,d.dosage_form_name as dosageFormName,d.object_type as objectType,d.object_name as objectName,d.object_drug_id as objectDrugId,");
                sql.append("d.status,d.object_id as objectId,dc.check_status as checkStatus,dc.contrast_id as contrastId,bd.specification as baseSpecification,");
                sql.append("bc.drug_name as drugName,bf.short_name as orgShortName,bf.factory_name as factoryName,bf.factory_code as factoryCode ");
                if ("1".equals(qo.getIsMatch())) {
                    sql.append("from (select * from base_org_drug where object_drug_id in(select object_drug_id from base_drug_contrast where delete_status='0')) d ");
                } else if ("0".equals(qo.getIsMatch())) {
                    sql.append("from (select * from base_org_drug where object_drug_id not in(select object_drug_id from base_drug_contrast where delete_status='0')) d ");
                } else {
                    sql.append("from base_org_drug d ");
                }
                sql.append("left join base_drug_contrast dc on d.object_drug_id=dc.object_drug_id ");
                sql.append("left join base_drug bd on bd.drug_id=dc.drug_id ");
                sql.append("left join base_currency_drug bc on bc.drug_info_id=bd.drug_info_id ");
                sql.append("left join base_factory bf on bf.factory_id=bd.factory_id ");
                sql.append("where d.delete_status='0' and (dc.delete_status='0' or dc.delete_status is null) ");
                if (StringUtils.isNotEmpty(qo.getRegionCode())) {
                    sql.append(" and d.object_id like '").append(qo.getRegionCode()).append("%' ");
                }
                if (StringUtils.isNotEmpty(qo.getObjectId())) {
                    sql.append(" and d.object_id=:objectId ");
                }
                if (StringUtils.isNotEmpty(qo.getCheckStatus())) {
                    sql.append(" and dc.check_status=:checkStatus ");
                }
                if (StringUtils.isNotEmpty(qo.getOrgDrugName())) {
                    sql.append(" and d.org_drug_name like '%").append(qo.getOrgDrugName()).append("%' ");
                }
                if (StringUtils.isNotEmpty(qo.getPinyinCode())) {
                    sql.append(" and d.pinyin_code like '%").append(qo.getPinyinCode()).append("%' ");
                }
                if (StringUtils.isNotEmpty(qo.getOrgFactoryName())) {
                    sql.append(" and d.org_factory_name like '%").append(qo.getOrgFactoryName()).append("%' ");
                }

                Query query = ss.createSQLQuery(sql.toString());
                if (StringUtils.isNotEmpty(qo.getObjectId())) {
                    query.setParameter("objectId", qo.getObjectId());
                }
                if (StringUtils.isNotEmpty(qo.getCheckStatus())) {
                    query.setParameter("checkStatus", qo.getCheckStatus());
                }
                query.setFirstResult((qo.getPageNo() - 1) * qo.getPageSize());
                query.setMaxResults(qo.getPageSize());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<OrgRegionVo> orgRegionList = new ArrayList<OrgRegionVo>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        orgRegionList.add(ConversionUtils.convert(map, OrgRegionVo.class));
                    }
                }
                setResult(orgRegionList);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public BigInteger getOrgRegionDrugCount(final OrgRegionQo qo) {
        HibernateStatelessResultAction<BigInteger> action = new AbstractHibernateStatelessResultAction<BigInteger>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select count(1) ");
                if ("1".equals(qo.getIsMatch())) {
                    sql.append("from (select * from base_org_drug where object_drug_id in(select object_drug_id from base_drug_contrast where delete_status='0')) d ");
                } else if ("0".equals(qo.getIsMatch())) {
                    sql.append("from (select * from base_org_drug where object_drug_id not in(select object_drug_id from base_drug_contrast where delete_status='0')) d ");
                } else {
                    sql.append("from base_org_drug d ");
                }
                sql.append("left join base_drug_contrast dc on d.object_drug_id=dc.object_drug_id ");
                sql.append("left join base_drug bd on bd.drug_id=dc.drug_id ");
                sql.append("left join base_currency_drug bc on bc.drug_info_id=bd.drug_info_id ");
                sql.append("left join base_factory bf on bf.factory_id=bd.factory_id ");
                sql.append("where d.delete_status='0' and (dc.delete_status='0' or dc.delete_status is null) ");
                if (StringUtils.isNotEmpty(qo.getRegionCode())) {
                    sql.append(" and d.object_id like '").append(qo.getRegionCode()).append("%' ");
                }
                if (StringUtils.isNotEmpty(qo.getObjectId())) {
                    sql.append(" and d.object_id=:objectId ");
                }
                if (StringUtils.isNotEmpty(qo.getCheckStatus())) {
                    sql.append(" and dc.check_status=:checkStatus ");
                }
                if (StringUtils.isNotEmpty(qo.getOrgDrugName())) {
                    sql.append(" and d.org_drug_name like '%").append(qo.getOrgDrugName()).append("%' ");
                }
                if (StringUtils.isNotEmpty(qo.getPinyinCode())) {
                    sql.append(" and d.pinyin_code like '%").append(qo.getPinyinCode()).append("%' ");
                }
                if (StringUtils.isNotEmpty(qo.getOrgFactoryName())) {
                    sql.append(" and d.org_factory_name like '%").append(qo.getOrgFactoryName()).append("%' ");
                }

                SQLQuery sqlQuery = ss.createSQLQuery(sql.toString());
                if (StringUtils.isNotEmpty(qo.getObjectId())) {
                    sqlQuery.setParameter("objectId", qo.getObjectId());
                }
                if (StringUtils.isNotEmpty(qo.getCheckStatus())) {
                    sqlQuery.setParameter("checkStatus", qo.getCheckStatus());
                }
                List list = sqlQuery.list();
                if (list.size() == 1) {
                    Object o = list.get(0);
                    BigInteger count = (BigInteger) o;
                    setResult(count);
                }
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<BaseOrgDrug> findByObjectDrugIds(List<Integer> objectDrugIds) {
        HibernateStatelessResultAction<List<BaseOrgDrug>> action = new AbstractHibernateStatelessResultAction<List<BaseOrgDrug>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append(" select bod.object_drug_id,bd.trade_name as orgDrugName from base_org_drug bod left join base_drug_contrast bdc on bod.object_type = bdc.object_type and bod.object_id=bdc.object_id\n" +
                        " left join base_drug bd on bd.drug_id=bdc.drug_id\n" +
                        " where bdc.check_status='1' and bod.status='1' and bdc.delete_status='0' and bod.object_drug_id in (:objectDrugIds);");
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameterList("objectDrugIds", objectDrugIds);
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<BaseOrgDrug> arrayList = new ArrayList<>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        BaseOrgDrug convert = ConversionUtils.convert(map, BaseOrgDrug.class);
                        arrayList.add(convert);
                        break;
                    }
                }
                setResult(arrayList);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public Boolean updates(List<BaseOrgDrug> baseOrgDrugs, String userId) {
        HibernateStatelessResultAction<Boolean> action = new AbstractHibernateStatelessResultAction<Boolean>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("");
                Query query = ss.createSQLQuery(sql.toString());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<BaseDrugBeanVo> arrayList = new ArrayList<>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        BaseDrugBeanVo convert = ConversionUtils.convert(map, BaseDrugBeanVo.class);
                        arrayList.add(convert);
                        break;
                    }
                }
                setResult(true);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public List<BaseDrugBeanVo> findBaseDrugs(BaseDrugManagementQo request, List<String> orgIds, final boolean isAdmin) {
        HibernateStatelessResultAction<List<BaseDrugBeanVo>> action = new AbstractHibernateStatelessResultAction<List<BaseDrugBeanVo>>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select d.object_drug_id as id,\n" +
                        "       d.org_drug_name as orgDrugName,\n" +
                        "       d.org_drug_id as orgDrugId,\n" +
                        "       d.dosage_form_code as dosageFormCode,\n" +
                        "       d.specification as specification,\n" +
                        "       bf.short_name as shortName,\n" +
                        "       bf.factory_name as factoryName,\n" +
                        "       d.pinyin_code as pinyinCode,\n" +
                        "       bc.dose_unit as doseUnit,\n" +
                        "       d.price as price,\n" +
                        "       d.object_name as objectName,bc.drug_name as drugName\n" +
                        "  from base_org_drug d\n" +
                        "         left join base_drug_contrast dc on d.object_drug_id = dc.object_drug_id\n" +
                        "         left join base_drug bd on bd.drug_id = dc.drug_id\n" +
                        "         left join base_currency_drug bc on bc.drug_info_id = bd.drug_info_id\n" +
                        "         left join base_factory bf on bf.factory_id = bd.factory_id\n" +
                        "  where dc.check_status = '1'\n" +
                        "  and d.status='1' and dc.delete_status = '0' and d.delete_status ='0' ");
                if (!StringUtil.isEmpty(request.getUniversalName())) {
                    sql.append("and d.org_drug_name like '%" + request.getUniversalName().trim() + "%' ");
                }
                if (!StringUtil.isEmpty(request.getPinyinCode())) {
                    sql.append("and d.pinyin_code like '%" + request.getPinyinCode().trim() + "%' ");
                }
                if (!StringUtil.isEmpty(request.getFactoryName())) {
                    sql.append("and bf.factory_name like '%" + request.getFactoryName().trim() + "%' ");
                }
                if (!StringUtil.isEmpty(request.getId())) {
                    sql.append("and d.object_id='" + request.getId().trim() + "'");
                }
                if (StringUtil.isEmpty(request.getId()) && !isAdmin){
                    sql.append("and d.object_id in (:orgIds)");
                }
                sql.append("and d.object_type=:objectType ");
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameter("objectType", request.getObjectType());
                if (StringUtil.isEmpty(request.getId()) && !isAdmin) {
                    query.setParameterList("orgIds",orgIds);
                }
                int first = request.getPageNo() > 0 ? (request.getPageNo() - 1) * request.getPageSize() : 0;
                query.setFirstResult(first);
                query.setMaxResults(request.getPageSize());
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<BaseDrugBeanVo> arrayList = new ArrayList<>();
                List<Map> list = query.list();
                if (list.size() > 0) {
                    for (Map map : list) {
                        BaseDrugBeanVo convert = ConversionUtils.convert(map, BaseDrugBeanVo.class);
                        arrayList.add(convert);
                    }
                }
                setResult(arrayList);
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

    public Integer getBaseDrugCount(BaseDrugManagementQo request, List<String> orgIds, final boolean isAdmin) {
        HibernateStatelessResultAction<Integer> action = new AbstractHibernateStatelessResultAction<Integer>() {
            @Override
            public void execute(StatelessSession ss) throws Exception {
                StringBuffer sql = new StringBuffer();
                sql.append("select  count(1) as countValue from base_org_drug d\n" +
                        "         left join base_drug_contrast dc on d.object_drug_id = dc.object_drug_id\n" +
                        "         left join base_drug bd on bd.drug_id = dc.drug_id\n" +
                        "         left join base_currency_drug bc on bc.drug_info_id = bd.drug_info_id\n" +
                        "         left join base_factory bf on bf.factory_id = bd.factory_id\n" +
                        "  where dc.check_status = '1'\n" +
                        "  and d.status='1' and dc.delete_status = '0' and d.delete_status ='0' ");
                if (!StringUtil.isEmpty(request.getUniversalName())) {
                    sql.append("and d.org_drug_name like '%" + request.getUniversalName().trim() + "%' ");
                }
                if (!StringUtil.isEmpty(request.getPinyinCode())) {
                    sql.append("and d.pinyin_code like '%" + request.getPinyinCode().trim() + "%' ");
                }
                if (!StringUtil.isEmpty(request.getFactoryName())) {
                    sql.append("and bf.factory_name like '%" + request.getFactoryName().trim() + "%' ");
                }
                if (!StringUtil.isEmpty(request.getId())) {
                    sql.append("and d.object_id='" + request.getId().trim() + "'");
                }
                if (StringUtil.isEmpty(request.getId()) && !isAdmin){
                    sql.append("and d.object_id in (:orgIds)");
                }
                sql.append("and d.object_type=:objectType ");
                Query query = ss.createSQLQuery(sql.toString());
                query.setParameter("objectType", request.getObjectType());
                if (StringUtil.isEmpty(request.getId()) && !isAdmin) {
                    query.setParameterList("orgIds",orgIds);
                }
                query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                List<Map> list = query.list();
                setResult(CommonUtils.isEmpty(list) ? 0 : ((BigInteger) ((Map) list.get(0)).get("countValue")).intValue());
            }
        };
        HibernateSessionTemplate.instance().execute(action);
        return action.getResult();
    }

}